from resource.context import PageRequest, PageResponse
from resource.context import *



class CategoryListReq(PageRequest):
    def __init__(self,
                 page, size,
                 category_no=None,
                 category_name=None,
                 category_alias=None,
                 status=-1,
                 level=-1
                 ):
        super().__init__(page, size)
        self.category_no = category_no
        self.category_name = category_name
        self.category_alias = category_alias
        self.level = level
        self.status = status


class CategoryService:
    def __init__(self, pool):
        self.pool = pool

    def cursor(self):
        conn = self.pool.get_connection()
        return conn.cursor(),conn

    def conn_close(self,cursor,conn):
        cursor.close()
        conn.close()

    # 分类列表
    def categoryList(self, param: CategoryListReq):
        cursor,conn = self.cursor()
        where =  ' where 1=1 '
        queryParam = []
        if (param.category_name is not None and param.category_name != ''):
            where += ' and t.category_name like %s'
            queryParam.append('%' + param.category_name + '%')
        if (param.level > 0):
            where += ' and t.category_level = %s'
            queryParam.append(param.level)
        if (param.status > 0):
            where += ' and t.category_status = ' + str(param.status)
        if (param.category_no is not None and param.category_no != ''):
            where += ' and t.category_no like %s'
            queryParam.append('%' + param.category_no + '%')
        if (param.category_alias is not None and param.category_alias != ''):
            where += ' and t.category_alias like %s'
            queryParam.append('%' + param.category_alias + '%')

        select_data_sql = 'SELECT t.id,t.category_no,t.category_name,t.category_level,t.category_spell,t.category_alias,t.scientific_name,' \
                          't.category_remark,t.category_status FROM category t'
        if (param.level == 2):
            select_data_sql = 'SELECT t.id,t.category_no,t.category_name,t.category_level,t.category_spell,t.category_alias,t.scientific_name,' \
                              't.category_remark,t.category_status, t2.category_name as t2_name FROM category t left join category t2 on t.parent_id = t2.id '
        if (param.level == 3):
            select_data_sql = 'SELECT t.id,t.category_no,t.category_name,t.category_level,t.category_spell,t.category_alias,t.scientific_name,' \
                              't.category_remark,t.category_status,t2.category_name as t2_name,t3.category_name as t3_name' \
                              ' FROM category t left join category t2 on t.parent_id = t2.id left join category t3 on t2.parent_id = t3.id '

        select_data_sql += where
        select_data_sql += ' order by t.id desc '
        select_data_sql += ' limit ' + str(param.take()) + ',' + str(param.size)

        cursor.execute(select_data_sql, queryParam)
        data = cursor.fetchall()
        select_all_sql = 'select count(t.id) from category t ' + where
        cursor.execute(select_all_sql, queryParam)
        count = cursor.fetchone()[0]
        self.conn_close(cursor,conn)
        return PageResponse(data, count, param.page, param.size)

    def categorySimpleList(self, level, parentId,cateNo = None):
        cursor,conn = self.cursor()
        select_data_sql = 'SELECT id,category_name,category_level,parent_id,category_no FROM category where parent_id = %s and category_level = %s order by id desc '
        cursor.execute(select_data_sql, (parentId,level))
        data = cursor.fetchall()
        jsonData = fetch2Json(data, cursor.description)

        for i in jsonData:
            i['selected'] = 0
            if(cateNo is not None and len(cateNo) > 0 and cateNo.startswith( i['category_no'])):
                i['selected'] = 1
        self.conn_close(cursor,conn)
        return jsonData

    def detail(self, id):
        cursor,conn = self.cursor()
        select_data_sql = 'select id,category_no,category_name,category_level,category_spell,parent_id,category_alias,scientific_name,category_remark,category_status,create_time,update_time,create_by,update_by from category where id = %s'
        cursor.execute(select_data_sql, (id))
        data = cursor.fetchall()
        jsonData = fetch2Json(data, cursor.description)
        self.conn_close(cursor,conn)
        return jsonData[0]

    def add(self, form):
        cursor,conn = self.cursor()
        category_name = form.get('category_name', '', str)
        category_spell = form.get('category_spell', '', str)
        category_alias = form.get('category_alias', '', str)
        scientific_name = form.get('scientific_name', '', str)
        category_remark = form.get('category_remark', '', str)
        parent_id = form.get('parent_id', -1, int)
        category_level = form.get('category_level', 1, int)

        category_no = self.generateNo(level=category_level, parentId=parent_id)

        insert_user_sql = 'INSERT INTO `category`(`category_no`, `category_name`, `category_level`, `category_spell`, `parent_id`, `category_alias`, ' \
                          '`scientific_name`, `category_remark`, `category_status`, `create_time`, `update_time`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s,%s, now(), now())'
        cursor.execute(insert_user_sql,
                       (category_no, category_name, category_level, category_spell, parent_id, category_alias,
                        scientific_name, category_remark, 1,))
        cursor.connection.commit()
        self.conn_close(cursor,conn)

    def edit(self, form):
        cursor,conn = self.cursor()
        id = form.get('id', -1, int)
        oldData = self.detail(id)
        category_name = form.get('category_name')
        category_level = form.get('category_level', 1, int)
        category_spell = form.get('category_spell')
        parent_id = form.get('parent_id', -1, int)

        category_alias = form.get('category_alias')
        scientific_name = form.get('scientific_name')
        category_remark = form.get('category_remark')
        category_status = form.get('category_status', 1, int)

        category_no = form.get('category_no')
        if (parent_id != oldData['parent_id']):
            category_no = self.generateNo(level=category_level, parentId=parent_id)

        modify_sql = 'update category set category_no=%s, category_name=%s, category_level=%s,' \
                     ' category_spell=%s, parent_id=%s, category_alias=%s, ' \
                     ' scientific_name=%s, category_remark=%s, category_status=%s where id = %s'
        cursor.execute(modify_sql, (
            category_no, category_name, category_level, category_spell, parent_id, category_alias, scientific_name,
            category_remark, category_status, id,
        ))
        cursor.connection.commit()
        self.conn_close(cursor,conn)

    def drop(self, id):
        cursor,conn = self.cursor()

        check_sql = 'select count(1) from category where parent_id = %s'
        cursor.execute(check_sql, (id))
        count = cursor.fetchone()[0]
        if (count is not None and count > 0):
            raise RuntimeError('存在子节点，先进行删除')

        modify_sql = 'delete from category where id = %s'
        cursor.execute(modify_sql, (id))
        cursor.connection.commit()
        self.conn_close(cursor,conn)

    # 生成 category_no
    def generateNo(self, level, parentId):
        cursor,conn = self.cursor()
        select_all_sql = 'select max(category_no) from category where category_level = %s and parent_id = %s '
        cursor.execute(select_all_sql, (level, parentId))
        count = cursor.fetchone()
        if (count[0] is None):
            select_sql = 'select category_no from category where id = %s '
            cursor.execute(select_sql, (parentId))
            no = cursor.fetchone()
            parNo = '' if no is None else no[0]
            self.conn_close(cursor,conn)
            return parNo + '1'.zfill(CATEGORY_NO_LEVEL_LENGTH)
        else:
            count = count[0]
            no = int(count[-CATEGORY_NO_LEVEL_LENGTH:]) + 1
            self.conn_close(cursor,conn)
            return count[:-CATEGORY_NO_LEVEL_LENGTH] + str(no).zfill(CATEGORY_NO_LEVEL_LENGTH)



